#ifndef CONTEMPLATION_H
#define CONTEMPLATION_H
#include <QCoreApplication>
#include "Conn.h"
#include "objectid.h"
#include "relationshipinfo.h"
#include "baseobject.h"
#include "characteristic.h"
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
#include <QMutex>
#include <qDebug>
#include <iostream>
#include <string>
#include <set>
#include <list>


class Contemplation
{
    Conn conn;
    QSqlQuery query;
public:
    Contemplation()
    {
        qDebug() << "Creating DataBase interaction class";
    }

//**************************************************************************************************************
    // this method creates a table with the given name, assigns the id column, adds new table to the Tables table and
    // the new column to the TableColumns table.
    //*******************************************************************************************************
    int MakeTable(QString table, int type = 0)
    {
        int count = 0;
        int id = -1;
        try{
            query.exec(QString("select * from Tables where name='%0'").arg(table));
            //qDebug() << "The Table table consists of:";
            while(query.next())
            {
                count++;
                qDebug() << query.value(0).toString() << ", " << query.value(0).toString() << ", " << query.value(0).toString();
            }
        }
        catch (...)
        {
            qDebug() << "There was an error checking to see if the table already exists - ";
            return false;
        }

        if(count > 0)
        {
            qDebug() << "The query size is " << count;
            cin.get();
            qDebug() << QString("Table %1 already exists in the database.").arg(table);
            return false;
        }

        qDebug() << "Adding table " << table;

        try{
            query.exec(QString("create table %1(id INTEGER primary key ASC)").arg(table));

            query.exec(QString("insert into Tables (name, type, numColumns) values('%1',%2, 1)").arg(table).arg(type));
            query.exec(QString("SELECT MAX(id)FROM Tables"));
            count = 0;
            while(query.next())
            {
                id = query.value(0).toInt();
                count++;
            }
        }
        catch (...)
        {
            qDebug() << "There was an error adding the table - ";
            return false;
        }
        if(id == -1 || count != 1)
        {
            cout << "There was an error - too many tables with the same name in Tables table." << endl;
            return false;
        }
        query.exec(QString("insert into TableColumns (tableNumber, name, type, position, size) values(%0, 'id', 1, 0, 0 );").arg(id));
        return id;
    }
    //**************************************************************************************************************
        // this method creates a new relationship table
        //*******************************************************************************************************
    RelationshipInfo MakeRelationshipTable(QString relationship, int type = 2)
    {
        RelationshipInfo relInfo;
        int newId;
        int tableId;
        QString tableName;
        relationship = relationship.toLower();
        try{
            tableName = QString("rel_%0").arg(relationship);
            tableId = MakeTable(tableName, type);
            AddColumn(tableName, "subject_table");
            AddColumn(tableName, "subject_id");
            AddColumn(tableName, "object_table");
            AddColumn(tableName, "object_id");

            QString relTable = "RelationshipTrees";

            newId = AddTuple(relTable, "name", relationship);
            AddValue(relTable, "tableNumber", tableId, newId);
        }
        catch (...)
        {
            qDebug() << "There was an error adding the new relationship table - ";
            return relInfo;
        }
        relInfo.name = relationship;
        relInfo.relationshipID = newId;
        relInfo.table = tableId;
        relInfo.tableName = tableName;
        relInfo.type = type;

        return relInfo;
    }

//*********************************************************************************************************************8
    //this method drops the table and removes its components from the Tables table and the tableColumns table
    //****************************************************************************************************************
    bool DropTable(QString table)
    {
        table = table.toLower();
        if(table == "tables")
        {
            cout << "You are not allowed to drop this table" << endl;
            return false;
        }
        qDebug() << "Dropping table " << table;

        try{
            query.exec(QString("drop table if exists %1").arg(table));
        }
        catch (...)
        {
            qDebug() << "There was an error dropping the table - ";
            return false;
        }

        try{
            int i = 0;
            query.exec(QString("select id from Tables where name = '%0'").arg(table));
            while(query.next())
            {
                i = query.value(0).toInt();
            }
            query.exec(QString("delete from TableColumns where tableNumber = %0;").arg(i));
        }
        catch (...)
        {
            qDebug() << "There was an error removing table columns from the Columns table - ";
            return false;
        }

        try{
            query.exec(QString("delete from Tables where name = '%0';").arg(table));
        }
        catch (...)
        {
            qDebug() << "There was an error removing the table from the Table list - ";
            return false;
        }

        return true;
    }
//******************************************************************************************************8
    //this table adds a column to the given table, then adds that column information to theh tableColumns table
    //*******************************************************************************************************
    bool AddColumn(QString table, QString column, int type = 1, int size = 20)
    {
        int tableNumber = -1;
        int count = 0;
        int count2 = 0;
        int position = -1;
                //qDebug() << "Adding column " << column;
                try{
                    query.exec(QString("select id from Tables where name='%0'").arg(table));
                    while(query.next())
                    {
                        count++;
                        tableNumber = query.value(0).toInt();
                    }
                    if(count != 1 || tableNumber == -1)
                        throw 1;
                }
                catch (exception e)
                {
                    qDebug() << "There was an error finding the table while trying to add the column - ";

                    return false;
                }

                try{
                    count2 = 0;
                    query.exec(QString("select * from TableColumns where tableNumber=%0 and name='%1';").arg(tableNumber).arg(column));
                    while(query.next())
                    {
                        count2++;
                    }
                    if(count2 > 0)
                    {
                        cout << "The column already exists" << endl;
                        return false;
                    }
                }
                catch (...)
                {
                    qDebug() << "There was an error adding the column - this column already exists ";
                    return false;
                }

                try{
                    query.exec(QString("alter table %1 add column %2 %3;").arg(table).arg(column).arg(getVariableType(type, size)));
                    query.exec(QString("SELECT MAX(position)FROM TableColumns where tableNumber = %0;").arg(tableNumber));
                    while(query.next())
                    {
                        position = query.value(0).toInt();
                    }
                    position++;
                    query.exec(QString("insert into TableColumns (tableNumber, name, type, position, size) values(%1, '%2', %3, %4, %5);").arg(tableNumber).arg(column).arg(type).arg(position).arg(size));
                }
                catch (...)
                {
                    qDebug() << "There was an error adding the column - ";
                    return false;
                }

                incrementColumn(tableNumber);

                return true;
    }

//*******************************************************************************************************************
    // method adds a new tuple to the given table, where one data piece of information is given
    // method returns value of new int id
    //**************************************************************************************************************
    int AddTuple(QString table, QString column, QString value)
    {
        if(column == "id"){
            cout << "You are not allowed to alter the id column." << endl;
            return -1;
        }

        if(!isColumn(table, column))
            return -1;
        int id;

        //qDebug() << "Adding tuple ";
        try{
            query.exec(QString("insert into %1 (%2) values('%3')")
                .arg(table).arg(column).arg(value));
        }
        catch (...)
        {
            qDebug() << "There was an error adding the column - ";
            return -1;
        }
        query.exec(QString("SELECT MAX(id)FROM %1;").arg(table));
        while(query.next())
        {
            id = query.value(0).toInt();
        }
        return id;
    }

    int AddTuple(QString table, QString column, int value)
    {
        column.toLower();
        if(column == "id"){
            cout << "You are not allowed to alter the id column." << endl;
            return -1;
        }

        if(!isColumn(table, column))
            return -1;
        int id;

        //qDebug() << "Adding tuple ";
        try{
            query.exec(QString("insert into %1 (%2) values(%3)")
                .arg(table).arg(column).arg(value));
        }
        catch (...)
        {
            qDebug() << "There was an error adding the column - ";
            return -1;
        }
        query.exec(QString("SELECT MAX(id)FROM %1;").arg(table));
        while(query.next())
        {
            id = query.value(0).toInt();
        }
        return id;
    }

    int AddTuple(QString table)
    {
        int id;

        //qDebug() << "Adding tuple ";
        try{
            query.exec(QString("insert into %1 values();").arg(table));
        }
        catch (...)
        {
            qDebug() << "There was an error adding the row - ";
            return -1;
        }
        query.exec(QString("SELECT MAX(id)FROM %1;").arg(table));
        while(query.next())
        {
            id = query.value(0).toInt();
        }
        return id;
    }
    //*************************************************************************************************************
    // this method increments the column number in the Tables table by one
    //***********************************************************************************************************
    bool incrementColumn(int tableNumber)
    {
        try{
        int count = 0;
        int num = 0;
        //cout << "incrementing column count" << endl;
        query.exec(QString("select numColumns from Tables where id=%0;").arg(tableNumber));
        while(query.next())
        {
            num = query.value(0).toInt();
            count++;
        }
        if(count < 1)
        {
            cout << "Error while incrementing columns in table, That table doesn't exist in the Tables table" << endl;
            return false;
        }
        if(count > 1)
        {
            cout << "Error while incrementing columns in table, there are multiple tables by that name" << endl;
            return false;
        }
        num++;
        query.exec(QString("update Tables set numColumns = %0 where id = %1;").arg(num).arg(tableNumber));
        }
        catch (...)
        {
            cout << "Error incrementing column numbers in Tables" << endl;
        }

        return true;
    }

//*****************************************************************************************************************8
    //the following overloaded methods add a value to a table
    //************************************************************************************************************8
    bool AddValue(QString table, QString column, QString newValue, int id)
    {
        int tableNumber = -1;
        int type = -1;
        int count = 0;
        column.toLower();
        if(column == "id"){
            cout << "You are not allowed to alter the id column." << endl;
            return false;
        }

        query.exec(QString("select id from Tables where name = '%0';").arg(table));
        count = 0;
        while(query.next())
        {
            tableNumber = query.value(0).toInt();
            count++;
        }

        if(count != 1 || tableNumber == -1)
        {
            cout << "Error adding value" << endl;
            return false;
        }


        if(type != 2 && type != 4)
        {
            cout << "This column does not take a QString value" << endl;
            return false;
        }

        //qDebug() << "Adding value ";
        try{
            query.exec(QString("update %0 set %1 = '%2' where id = %3;")
                .arg(table).arg(column).arg(newValue).arg(id));
        }
        catch (...)
        {
            qDebug() << "There was an error adding the column - ";
            return false;
        }
        return true;
    }

    bool AddValue(QString table, QString column, int newValue, int id)
    {
        int tableNumber = -1;
        int type = -1;
        int count = 0;
        column.toLower();
        if(column == "id"){
            cout << "You are not allowed to alter the id column." << endl;
            return false;
        }

        query.exec(QString("select id from Tables where name = '%0';").arg(table));
        count = 0;
        while(query.next())
        {
            tableNumber = query.value(0).toInt();
            count++;
        }
        if(count != 1 || tableNumber == -1)
        {
            cout << "Error adding value" << endl;
            return false;
        }

        type = isColumn(tableNumber, column);


        //qDebug() << "Adding value ";
        try{
            query.exec(QString("update %0 set %1 = %2 where id = %3;")
                .arg(table).arg(column).arg(newValue).arg(id));
        }
        catch (...)
        {
            qDebug() << "There was an error adding the column - ";
            return false;
        }
        return true;
    }


     // **********************************************************************************************************************************
    //this method adds a new object to the database
    BaseObject addNewWord(int nPos, int text = NULL)
    {
        BaseObject bo;

        // check to see if the word and object are already in the database
        QList<int> textIdList;
        query.exec(QString("select subject_id from rel_text_to_word where object_id = %0;").arg(text));
        while (query.next())
        {
            textIdList.push_back(query.value(0).toInt());
        }

        bool found = false;
        for(int i = 0; i < textIdList.size(); i++)
        {
            query.exec(QString("select * from words where id = %0 and part_of_speech = %1;").arg(textIdList[i]).arg(nPos));
            while (query.next())
            {
                found = true;
                bo.id = query.value(0).toInt();
                bo.pos = query.value(1).toInt();
            }
        }


        //cout << "found is " << found << endl;
        if(found)
        {
            return bo;
        }

        bo.id = AddTuple("words", "part_of_speech", nPos);
        AddValue("words","total_leaves", 0, bo.id);
        bo.pos = nPos;

        if(text != NULL)
        {
            QString table = "rel_text_to_word";
            bo.table = tableNumber("words");
            int id = AddTuple(table, "object_table", bo.table);
            AddValue(table,"object_id", bo.id, id);
            int tn = tableNumber("text");
            AddValue(table, "subject_table", tn, id);
            AddValue(table, "subject_id", text, id);
        }

        return bo;
    }

    //****************************************************************************************
    // this method creates a new object and returns its id number
    ObjectID makeNewObject()
    {
        ObjectID id;
        id.id = AddTuple("objects","number", 1);
        id.table = tableNumber("objects");
        return id;
    }

    //*******************************************************************************************
    // this method returns the id number for a text word

    int getTextID(QString text)
    {
        int id = -1;

        query.exec(QString("select id from text where spelling = '%0';").arg(text));
        while (query.next())
        {
            id = query.value(0).toInt();
        }
        return id;
    }

    int getPosID(QString text)
    {
        int id = -1;

        query.exec(QString("select id from part_of_speech where name = '%0';").arg(text));
        while (query.next())
        {
            id = query.value(0).toInt();
        }
        return id;
    }

    // This method returns a list of texts for a given word object
    QList<QString> getTextForWord(int word)
    {
        QList<QString> texts;
        QList<int> textints;

        query.exec(QString("select subject_id from rel_text_to_word where object_id = %0;").arg(word));
        while(query.next())
        {
            textints.push_back(query.value(0).toInt());
        }

        for(int i = 0; i < textints.size(); i++)
        {
            query.exec(QString("select spelling from text where id = %0;").arg(textints[i]));
            while(query.next())
            {
                texts.push_back(query.value(0).toString());
            }
        }


        return texts;
    }


    // **********************************************************************************************************************************
    // This method returns the number of columns for a table as saved in the Tables table

    int numberOfColumns(QString table)
    {
        int p = 0;
        int count = 0;
        query.exec(QString("select numColumns from Tables where name = '%0';").arg(table));
        while (query.next())
        {
            p = query.value(0).toInt();
            count++;
        }

        if(count != 1)
        {
            qDebug() << QString("There was an error calculating the number of columns in %1").arg(table);
            return 0;
        }
        return p;
    }


    //*******************************************************************************************************************************8
    // this method checks to see if a column exists within a table and returns its value type
    int isColumn(int tableNumber, QString column)
    {
        int count;
        int type;
        query.exec(QString("select type from TableColumns where name = '%0' and tableNumber = %1;").arg(column).arg(tableNumber));
        count = 0;
        while(query.next())
        {
            type = query.value(0).toInt();
            count++;
        }

        if(count != 1 || type == -1)
        {
            cout << "Error - there doesn't seem to be a column by that name" << endl;
            return -1;
        }

        //qDebug() << "type is " << type;
//        if(type != 2 && type != 4)
//        {
//            cout << "This column does not take a QString value" << endl;
//            return false;
//        }
        return type;
    }

    int isColumn(QString table, QString column)
    {
        int count;
        int type;
        int tableNumber = -1;
        query.exec(QString("select id from Tables where name = '%0';").arg(table));
        count = 0;
        while(query.next())
        {
            tableNumber = query.value(0).toInt();
            count++;
        }
        if(count != 1 || tableNumber == -1)
        {
            cout << "Error adding value" << endl;
            return false;
        }
        query.exec(QString("select type from TableColumns where name = '%0' and tableNumber = %1;").arg(column).arg(tableNumber));
        count = 0;
        while(query.next())
        {
            type = query.value(0).toInt();
            count++;
        }

        if(count != 1 || type == -1)
        {
            cout << "Error - there doesn't seem to be a column by that name" << endl;
            return -1;
        }

        //qDebug() << "type is " << type;
//        if(type != 2 && type != 4)
//        {
//            cout << "This column does not take a QString value" << endl;
//            return false;
//        }
        return type;
    }



    // **********************************************************************************************************************************
    // This method displays the entire contents of the Tables table
    void showTables()
    {
        query.exec(QString("select * from Tables;"));
        while (query.next())
        {
            qDebug() << query.value(0).toString() << ", " << query.value(1).toString() << ", " << query.value(2).toString() << ", " << query.value(3).toString();
        }
    }

    // **********************************************************************************************************************************
    // This method returns the id number of a table from the Tables table

    int tableNumber(QString table)
    {
        int p = 0;
        int count = 0;
        query.exec(QString("select id from Tables where name = '%0';").arg(table));
        while (query.next())
        {
            p = query.value(0).toInt();
            count++;
        }

        if(count != 1)
        {
            qDebug() << QString("There was an error retrieving the table number for %1").arg(table);
            return 0;
        }
        return p;
    }


    //********************************************************************************************************
    //This method prints out the contents of a given table

    void displayTable(QString table)
    {
        QString result;
        int j = numberOfColumns(table);

        query.exec(QString("select * from %1;").arg(table));
        while (query.next())
            {
            result = "";
                for(int i = 0; i < j; i++)
                {
                    result += query.value(i).toString() + ", ";
                }
                qDebug() << result;
                cout << endl;
            }
    }

    RelationshipInfo getRelTableInfo(QString relationship)
    {
        RelationshipInfo relInfo;
        relInfo.relationshipID = -1;
        relInfo.name = relationship;
        query.exec(QString("select id, tableNumber from RelationshipTrees where name = '%0';").arg(relationship));
        while(query.next())
        {
            relInfo.relationshipID = query.value(0).toInt();
            relInfo.table = query.value(1).toInt();
        }

        query.exec(QString("select name, type from Tables where id = %0;").arg(relInfo.table));
        while(query.next())
        {
            relInfo.tableName = query.value(0).toString();
            relInfo.type = query.value(1).toInt();
        }

        return relInfo;
    }

    QList<int> find()
    {
        QList<int> found;
        query.exec(QString("select id from rel_isarelationship where object_table = 9 and object_id = 2;"));
        while (query.next())
        {
             found.push_back(query.value(0).toInt());
        }


        return found;
    }

    //*************************************************************************************************************************
    // This method checks to see if a relationship is in the table or not

    bool relInTable(QString table, int subjectTable, int subjectID, int objectTable, int objectID)
    {
        bool exists = false;
        query.exec(QString("select * from %0 where subject_table = %1 and subject_id = %2 and object_table = %3 and object_id = %4;").arg(table).arg(subjectTable).arg(subjectID).arg(objectTable).arg(objectID));
        while(query.next())
        {
            cout << endl << "*****************************it is already there" << endl;
            exists = true;
        }
        return exists;
    }

    //*****************8
    // increases tally table by 1 for given word
    bool increaseTally(QString relation, BaseObject word, ObjectClass object)
    {
        // we need the table number for the relation
        //qDebug() << "we're in the increas tally method";
        QString tableName = "rel_";
        tableName.append(relation);
        int tableNum = tableNumber(tableName);
        bool alreadyThere = false;
        QString relTable = "rel_inductive_tally";

        // see if the relationship is already there, add 1
        query.exec(QString("select id, num_for from rel_inductive_tally where rel_table = %0 and word_id = %1 and object_table = %2 and object_id = %3;").arg(tableNum).arg(word.id).arg(object.table).arg(object.id));
        while(query.next())
        {
            alreadyThere = true;
            //qDebug() << "the relationship was already there";
            int id = query.value(0).toInt();
            int numFor = query.value(1).toInt();
            numFor++;
            AddValue(relTable, "num_for", numFor, id);
        }

        if(!alreadyThere)
        {
            // add new tuple and set to 1

            //qDebug() << "object table is " << object.table << " and id is " << object.id << "**********************";
            //qDebug() << "adding new tuple";
            int newID = AddTuple("rel_inductive_tally", "word_id", word.id);
            AddValue("rel_inductive_tally", "num_for", 1, newID);
            AddValue("rel_inductive_tally", "rel_table", tableNum, newID);
            AddValue("rel_inductive_tally", "object_table", object.table, newID);
            AddValue("rel_inductive_tally", "object_id", object.id, newID);
            AddValue("rel_inductive_tally", "num_against", 0, object.id);
        }

        return true;
    }

    //*************************************************************************************************************************
    // This method checks to see if an object relationship is associated with a word in the inductive relationship list, adds 1 to the tally if it is and adds it if not
    bool tallyRelations(BaseObject bos, ObjectClass id)
    {
        //cout << "we;re doing the tally thing" << endl;




        // get a list of all of the relationship tables
        QList<QString> relTableList = relTableNames();
        for (int i = 0; i < relTableNames().size(); i++)
        {
            // get a list of all of the objects in the table that are related to id

            QList<ObjectID> relatedTo;


            int tempId;
            int tempNumFor;
            //int tempNumTotal;
            QString tableName = relTableList[i];
            int tableNum = tableNumber(tableName);


            query.exec(QString("select object_table, object_id from %0 where subject_table = %1 and subject_id = %2;").arg(tableName).arg(id.table).arg(id.id));
            while(query.next())
            {
                ObjectID temp; // this is the object that needs to be added to the inductive table
                temp.table = query.value(0).toInt();
                temp.id = query.value(1).toInt();
                relatedTo.push_back(temp);

                // check to see if the tuple is already in the table, and add 1 if so
                int count = 0;
                query.exec(QString("select id, num_for from rel_inductive_tally where object_table = %0 and object_id = %1 and word_id = %2 and rel_table = %4;").arg(temp.table).arg(temp.id).arg(bos.id).arg(tableNum));
                while(query.next())
                {
                    count++;
                    tempId = query.value(0).toInt();
                    tempNumFor = query.value(1).toInt();
                    tempNumFor++;
                    AddValue("rel_inductive_tally", "num_for", tempNumFor, tempId);
                }
                if(count == 0)
                {
                    int newid = AddTuple("rel_inductive_tally", "word_id", bos.id);
                    AddValue("rel_inductive_tally","rel_table", tableNum,newid);
                    AddValue("rel_inductive_tally","object_table", temp.table, newid);
                    AddValue("rel_inductive_tally","object_id", temp.id, newid);
                    AddValue("rel_inductive_tally","num_for", 1,newid);
                }
            }
        }
        return true;
    }

    //*************************************************************************************************************88
    // this method takes an object and returns all of the words it's a member of
    QList<BaseObject> UpWordTrees(ObjectClass id)
    {
        QList<BaseObject> isas;
        QList<BaseObject> isas2;
        int isasCount = 0;
        query.exec(QString("select object_table, object_id from rel_object_to_word where subject_table = %0 and subject_id = %1;").arg(id.table).arg(id.id));
        while(query.next())
        {
            BaseObject tempObject;
            tempObject.table = query.value(0).toInt();
            tempObject.id = query.value(1).toInt();
            isas.push_back(tempObject);
        }

        do
        {

            isas2.clear();
            if(isas2.size() != 0)
                qDebug() << "how can this be??";
            for(int i = isasCount; i < isas.size(); i++)
            {
                query.exec(QString("select object_table, object_id from rel_isarelationship where subject_table = %0 and subject_id = %1;").arg(isas[i].table).arg(isas[i].id));
                while(query.next())
                {
                    BaseObject tempObject2;
                    tempObject2.table = query.value(0).toInt();
                    tempObject2.id = query.value(1).toInt();
                    isas2.push_back(tempObject2);
                }
            }
            isasCount = isas.size();
            isas.append(isas2);
        }
        while(isas2.size() > 0);






        return isas;
    }

    //*************************************************************************************************************************????????????????????????
    // This method increments the word total by 1 in the words table
    bool incrementWordLeaves(int wordId)
    {
        //qDebug() << "We're in the increment function and the wordId is " << wordId;
        try{
            //int count = 0;
            //query.exec(QString("select total_leaves from words where id = wordId;"));
            query.exec(QString("select total_leaves from words where id = %0;").arg(wordId));
            while(query.next())
            {
                //count++;
                int temp = query.value(0).toInt();
                //qDebug() << "the value starts as " << temp;
                temp++;
                //qDebug() << "the becomes " << temp;
                AddValue("words", "total_leaves", temp, wordId);
            }
            //qDebug() << "the count in the method is " << count;
        }
        catch (...)
        {
            cout << "Error - couldn't increment word leaves";
            return false;
        }
        return true;
    }

    //*********************************************************************************************************************
    // This method adds the relationships from an object to the word
    bool addToTally(QString relation, ObjectClass subject, ObjectClass object)
    {
        qDebug() << "adding to tally";
        int relTableNum = tableNumber(relation);
        // first, get a list of all of the words associated with the subject in the relateobjecttoword table
        // for each memeber of word ids, we add the new object info to the inductive tally table, making the number for 1 or adding to existing
        QList<int> onlyWords;
        query.exec(QString("select object_id from relate_object_to_word where subject_table = %0 and subject_id = %1;").arg(subject.table).arg(subject.id));
        while(query.next())
        {
            onlyWords.push_back(query.value(0).toInt());
        }

        for(int i = 0; i < onlyWords.size(); i++)
        {
            int count = 0;
            int wordId = onlyWords[i];
            query.exec(QString("select id, num_for from rel_inductive_tally where word_id = %0 and rel_table = %1 and object_table = %2 and object_id = %3;").arg(wordId).arg(relTableNum).arg(object.table).arg(object.id));
            while(query.next())
            {
                count++;
                // it's already in the table, so add 1 to the num_for column
                int tempid = query.value(0).toInt();
                int tempFor = query.value(1).toInt();
                tempFor++;
                AddValue("rel_inductive_tally", "num_for", tempFor, tempid);
            }
            if(count = 0)
            {
                int newid = AddTuple("rel_inductive_tally", "word_id", wordId);
                AddValue("rel_inductive_tally","rel_table", relTableNum,newid);
                AddValue("rel_inductive_tally","object_table", object.table, newid);
                AddValue("rel_inductive_tally","object_id", object.id, newid);
                AddValue("rel_inductive_tally","num_for", 1,newid);
            }
        }
    }

    //*************************************************************************************************************************
    // This method returns a string list of all the relationship table names
    QList<QString> relTableNames()
    {
        QList<QString> relationships;
        query.exec(QString("select name from Tables where type = 2 or type = 3 or type = 4;"));
        while(query.next())
        {
            relationships.push_back(query.value(0).toString());
        }
        return relationships;
    }


    //***********************************************************************************************************************
    // this method gets a list of the characteristics for a word
    QList<Characteristic> getCharacteristic(BaseObject bo)
    {
        QList<Characteristic> characters;
        query.exec(QString("select * from rel_inductive_tally where word_id = %0").arg(bo.id));
        while(query.next())
        {
            Characteristic ch;
            ch.word_id = query.value(1).toInt();
            ch.rel_table = query.value(2).toInt();
            ch.object_table = query.value(3).toInt();
            ch.object_id = query.value(4).toInt();
            ch.num_for = query.value(5).toInt();
            ch.num_against = query.value(6).toInt();
            characters.push_back(ch);
        }

        return characters;
    }

    //***********************************************************************************************************************
    // this method returns the first single integer value from requested query
    int getIntValueFromTable(QString table, QString fromColumn, QString whereColumn, int value)
    {
        int response = 0;

        query.exec(QString("select %0 from %1 where %2 = %3").arg(fromColumn).arg(table).arg(whereColumn).arg(value));
        while(query.next())
        {
            response = query.value(0).toInt();
            return response;
        }

        return response;
    }

//******************************************************************************************************************************
// This method clears all tables in the database, adds the Tables and TableColumns tables back in.
// Also adds relationship table with name, table, directions, and qualifiers, and procedures table
//    This resets the entire system.
//******************************************************************************************************************************
    int clearDatabase()
    {
        QList<QString> destroy;

        query.exec(QString("select name from Tables;"));
        while (query.next())
        {

            destroy.push_back(query.value(0).toString());
            qDebug() << query.value(0).toString();
            //DropTable(query.value(0).toString());
        }

        for(int i = 0; i < destroy.size(); i++)
        {
            //cout << "another one bites the dust " << i << endl;
            DropTable(destroy[i]);
        }

        query.exec("drop table if exists Tables;");

        query.exec(QString("create table Tables(id INTEGER primary key ASC, name varchar(20) unique, type int, numColumns int);"));
        // type is 0 for unknown
        // type is 1 for organizational
        // type is 2 for relationship with unknown type
        // type is 3 for simple 1 to 1 relationship
        // type is 4 for directional 1 to 1 relationship
        // type is 5 for complex tree relationship
        // type is 6 for object instance

        //
        query.exec(QString("create table TableColumns(id INTEGER primary key ASC, tableNumber int, name varchar(20), type int, position int, size int);"));
        query.exec(QString("insert into Tables (name, type, numColumns) values('TableColumns', 1, 6);"));

        query.exec(QString("create table RelationshipTrees(id INTEGER primary key ASC, name varchar(20), tableNumber int, move1 int, move2 int, exlevel int, cases int, supporting int, exceptions int);"));
        // defines the moves that makes a relationship within a tree. move1 and move2 may be neg to indicate upward movement, exlevel indicates at which level it's exclusive (siblings is exclsive at 1, cousins at 2, self and siblings at 0)
        query.exec(QString("insert into Tables (name, type, numColumns) values('RelationshipTrees', 1, 9);"));

        int tableNum = tableNumber("TableColumns");

        query.exec(QString("insert into TableColumns (tableNumber, name, type, position, size) values(%0, 'id', 1, 0, 0 );").arg(tableNum));
        query.exec(QString("insert into TableColumns (tableNumber, name, type, position, size) values(%0, 'tableNumber', 1, 1, 0 );").arg(tableNum));
        query.exec(QString("insert into TableColumns (tableNumber, name, type, position, size) values(%0, 'name', 2, 2, 20 );").arg(tableNum));
        query.exec(QString("insert into TableColumns (tableNumber, name, type, position, size) values(%0, 'type', 1, 3, 0 );").arg(tableNum));
        query.exec(QString("insert into TableColumns (tableNumber, name, type, position, size) values(%0, 'position', 1, 4, 0 );").arg(tableNum));
        query.exec(QString("insert into TableColumns (tableNumber, name, type, position, size) values(%0, 'size', 1, 5, 0 );").arg(tableNum));

        tableNum = tableNumber("RelationshipTrees");

        query.exec(QString("insert into TableColumns (tableNumber, name, type, position, size) values(%0, 'id', 1, 0, 0 );").arg(tableNum));
        query.exec(QString("insert into TableColumns (tableNumber, name, type, position, size) values(%0, 'name', 2, 1, 20 );").arg(tableNum));
        query.exec(QString("insert into TableColumns (tableNumber, name, type, position, size) values(%0, 'tableNumber', 1, 2, 0 );").arg(tableNum));
        query.exec(QString("insert into TableColumns (tableNumber, name, type, position, size) values(%0, 'move1', 1, 3, 0 );").arg(tableNum));
        query.exec(QString("insert into TableColumns (tableNumber, name, type, position, size) values(%0, 'move2', 1, 4, 0 );").arg(tableNum));
        query.exec(QString("insert into TableColumns (tableNumber, name, type, position, size) values(%0, 'exlevel', 1, 5, 0 );").arg(tableNum));
        query.exec(QString("insert into TableColumns (tableNumber, name, type, position, size) values(%0, 'cases', 1, 6, 0 );").arg(tableNum));
        query.exec(QString("insert into TableColumns (tableNumber, name, type, position, size) values(%0, 'suppporting', 1, 7, 0 );").arg(tableNum));
        query.exec(QString("insert into TableColumns (tableNumber, name, type, position, size) values(%0, 'exceptions', 1, 8, 0 );").arg(tableNum));

        QString tableName = "words";
        MakeTable(tableName, 1);
        AddColumn(tableName, "part_of_speech");
        AddColumn(tableName, "total_leaves");

        tableName = "text";
        MakeTable(tableName, 1);
        AddColumn(tableName, "spelling", 2, 50);

        tableName = "isArelationship";
        MakeRelationshipTable(tableName, 4);


        tableName = "similar_to";
        MakeRelationshipTable(tableName, 3);

        tableName = "text_to_word";
        MakeRelationshipTable(tableName, 1);

        tableName = "word_to_meaning";
        MakeRelationshipTable(tableName, 1);

        tableName = "object_to_word";
        MakeRelationshipTable(tableName, 4);

        tableName = "object_to_description";
        MakeRelationshipTable(tableName, 4);

        tableName = "object_to_contains";
        MakeRelationshipTable(tableName, 4);

        tableName = "rel_pronunciation";
        MakeTable(tableName, 4);
        AddColumn(tableName,"text_id");
        AddColumn(tableName,"word_id");
        AddColumn(tableName,"pronunciation_id");

        tableName = "pronunciation";
        MakeTable(tableName, 1);
        AddColumn(tableName,"pronounce", 2);

        tableName = "objects";
        MakeTable(tableName, 1);
        AddColumn(tableName,"number");



        tableName = "rel_inductive_tally";
        MakeTable(tableName, 1);
        AddColumn(tableName,"word_id");
        AddColumn(tableName,"rel_table");
        AddColumn(tableName,"object_table");
        AddColumn(tableName,"object_id");
        AddColumn(tableName,"num_for");
        AddColumn(tableName,"num_against");

        //************ parts of speech section - do not add extra stuff below this point
        tableName = "part_of_speech";
        int posID = MakeTable(tableName, 1);
        AddColumn(tableName,"name", 2);

        // Add values to the parts of speech:
        int peak = 0;
        int end = 0;
        int middle = 0;

        query.exec(QString("insert into %0 (name) values('word');").arg(tableName));

        query.exec(QString("SELECT MAX(id)FROM %0").arg(tableName));
        while(query.next())
        {
            peak = query.value(0).toInt();
        }

        query.exec(QString("insert into %0 (name) values('noun');").arg(tableName));//2
        query.exec(QString("insert into %0 (name) values('verb');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('adjective');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('interjection');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('proper name');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('adverb');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('conjunction');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('preposition');").arg(tableName));//9

        query.exec(QString("SELECT MAX(id)FROM %0").arg(tableName));
        while(query.next())
        {
            end = query.value(0).toInt();
        }
        end++;
        middle = peak;
        for(int i = (middle + 1); i < end; i++)
        {
            query.exec(QString("insert into isArelationship (subject_table, subject_id, object_table, object_id) values(%0, %1, %0, %2);").arg(posID).arg(i).arg(peak));
        }

        // get highest current value


        middle = end;

        // get value of the object that is the common among all

        query.exec(QString("select id from %0 where name = 'noun';").arg(tableName));
        while(query.next())
        {
            peak = query.value(0).toInt();
        }

        // add in the new words that belong to the peak group

        query.exec(QString("insert into %0 (name) values('abstract');").arg(tableName));//10
        query.exec(QString("insert into %0 (name) values('collective');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('concrete');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('place');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('pronoun');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('person');").arg(tableName));//15

        // get the highest current value
        query.exec(QString("SELECT MAX(id)FROM %0").arg(tableName));
        while(query.next())
        {
            end = query.value(0).toInt();
        }
        end++;

        for(int i = middle; i < end; i++)
        {
            query.exec(QString("insert into isArelationship (subject_table, subject_id, object_table, object_id) values(%0, %1, %0, %2);").arg(posID).arg(i).arg(peak));
        }

        // end of process








        // get highest current value


        middle = end;

        // get value of the object that is the common among all

        query.exec(QString("select id from %0 where name = 'pronoun';").arg(tableName));
        while(query.next())
        {
            peak = query.value(0).toInt();
        }

        // add in the new words that belong to the peak group

        query.exec(QString("insert into %0 (name) values('personal');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('reflexive');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('possessive');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('relative');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('interrogative');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('demonstrative');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('indefinite');").arg(tableName));


        // get the highest current value
        query.exec(QString("SELECT MAX(id)FROM %0").arg(tableName));
        while(query.next())
        {
            end = query.value(0).toInt();
        }
        end++;

        for(int i = middle; i < end; i++)
        {
            query.exec(QString("insert into isArelationship (subject_table, subject_id, object_table, object_id) values(%0, %1, %0, %2);").arg(posID).arg(i).arg(peak));
        }

        // end of process








        // get highest current value


        middle = end;

        // get value of the object that is the common among all

        query.exec(QString("select id from %0 where name = 'preposition';").arg(tableName));
        while(query.next())
        {
            peak = query.value(0).toInt();
        }

        // add in the new words that belong to the peak group

        query.exec(QString("insert into %0 (name) values('time');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('place');").arg(tableName));

        // get the highest current value
        query.exec(QString("SELECT MAX(id)FROM %0").arg(tableName));
        while(query.next())
        {
            end = query.value(0).toInt();
        }
        end++;

        for(int i = middle; i < end; i++)
        {
            query.exec(QString("insert into isArelationship (subject_table, subject_id, object_table, object_id) values(%0, %1, %0, %2);").arg(posID).arg(i).arg(peak));
        }

        // end of process








        // get highest current value


        middle = end;

        // get value of the object that is the common among all

        query.exec(QString("select id from %0 where name = 'verb';").arg(tableName));
        while(query.next())
        {
            peak = query.value(0).toInt();
        }

        // add in the new words that belong to the peak group

        query.exec(QString("insert into %0 (name) values('auxilary');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('lexical');").arg(tableName));


        // get the highest current value
        query.exec(QString("SELECT MAX(id)FROM %0").arg(tableName));
        while(query.next())
        {
            end = query.value(0).toInt();
        }
        end++;

        for(int i = middle; i < end; i++)
        {
            query.exec(QString("insert into isArelationship (subject_table, subject_id, object_table, object_id) values(%0, %1, %0, %2);").arg(posID).arg(i).arg(peak));
        }

        // end of process




        // get highest current value


        middle = end;

        // get value of the object that is the common among all

        query.exec(QString("select id from %0 where name = 'auxilary';").arg(tableName));
        while(query.next())
        {
            peak = query.value(0).toInt();
        }

        // add in the new words that belong to the peak group

        query.exec(QString("insert into %0 (name) values('primary');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('modal');").arg(tableName));


        // get the highest current value
        query.exec(QString("SELECT MAX(id)FROM %0").arg(tableName));
        while(query.next())
        {
            end = query.value(0).toInt();
        }
        end++;

        for(int i = middle; i < end; i++)
        {
            query.exec(QString("insert into isArelationship (subject_table, subject_id, object_table, object_id) values(%0, %1, %0, %2);").arg(posID).arg(i).arg(peak));
        }

        // end of process






        // get highest current value


        middle = end;

        // get value of the object that is the common among all

        query.exec(QString("select id from %0 where name = 'lexical';").arg(tableName));
        while(query.next())
        {
            peak = query.value(0).toInt();
        }

        // add in the new words that belong to the peak group

        query.exec(QString("insert into %0 (name) values('dynamic');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('static');").arg(tableName));


        // get the highest current value
        query.exec(QString("SELECT MAX(id)FROM %0").arg(tableName));
        while(query.next())
        {
            end = query.value(0).toInt();
        }
        end++;

        for(int i = middle; i < end; i++)
        {
            query.exec(QString("insert into isArelationship (subject_table, subject_id, object_table, object_id) values(%0, %1, %0, %2);").arg(posID).arg(i).arg(peak));
        }

        // end of process




        // get highest current value


        middle = end;

        // get value of the object that is the common among all

        query.exec(QString("select id from %0 where name = 'static';").arg(tableName));
        while(query.next())
        {
            peak = query.value(0).toInt();
        }

        // add in the new words that belong to the peak group

        query.exec(QString("insert into %0 (name) values('belief');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('emotion');").arg(tableName));


        // get the highest current value
        query.exec(QString("SELECT MAX(id)FROM %0").arg(tableName));
        while(query.next())
        {
            end = query.value(0).toInt();
        }
        end++;

        for(int i = middle; i < end; i++)
        {
            query.exec(QString("insert into isArelationship (subject_table, subject_id, object_table, object_id) values(%0, %1, %0, %2);").arg(posID).arg(i).arg(peak));
        }

        // end of process



        // get highest current value


        middle = end;

        // get value of the object that is the common among all

        query.exec(QString("select id from %0 where name = 'interjection';").arg(tableName));
        while(query.next())
        {
            peak = query.value(0).toInt();
        }

        // add in the new words that belong to the peak group

        query.exec(QString("insert into %0 (name) values('greeting');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('expletive');").arg(tableName));


        // get the highest current value
        query.exec(QString("SELECT MAX(id)FROM %0").arg(tableName));
        while(query.next())
        {
            end = query.value(0).toInt();
        }
        end++;

        for(int i = middle; i < end; i++)
        {
            query.exec(QString("insert into isArelationship (subject_table, subject_id, object_table, object_id) values(%0, %1, %0, %2);").arg(posID).arg(i).arg(peak));
        }

        // end of process




        // get highest current value


        middle = end;

        // get value of the object that is the common among all

        query.exec(QString("select id from %0 where name = 'adjective';").arg(tableName));
        while(query.next())
        {
            peak = query.value(0).toInt();
        }

        // add in the new words that belong to the peak group

        query.exec(QString("insert into %0 (name) values('descriptive');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('limiting');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('predicate');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('verbal');").arg(tableName));


        // get the highest current value
        query.exec(QString("SELECT MAX(id)FROM %0").arg(tableName));
        while(query.next())
        {
            end = query.value(0).toInt();
        }
        end++;

        for(int i = middle; i < end; i++)
        {
            query.exec(QString("insert into isArelationship (subject_table, subject_id, object_table, object_id) values(%0, %1, %0, %2);").arg(posID).arg(i).arg(peak));
        }

        // end of process




        // get highest current value


        middle = end;

        // get value of the object that is the common among all

        query.exec(QString("select id from %0 where name = 'adverb';").arg(tableName));
        while(query.next())
        {
            peak = query.value(0).toInt();
        }

        // add in the new words that belong to the peak group

        query.exec(QString("insert into %0 (name) values('manner');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('place');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('time');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('degree');").arg(tableName));


        // get the highest current value
        query.exec(QString("SELECT MAX(id)FROM %0").arg(tableName));
        while(query.next())
        {
            end = query.value(0).toInt();
        }
        end++;

        for(int i = middle; i < end; i++)
        {
            query.exec(QString("insert into isArelationship (subject_table, subject_id, object_table, object_id) values(%0, %1, %0, %2);").arg(posID).arg(i).arg(peak));
        }

        // end of process



        // get highest current value


        middle = end;

        // get value of the object that is the common among all

        query.exec(QString("select id from %0 where name = 'conjunction';").arg(tableName));
        while(query.next())
        {
            peak = query.value(0).toInt();
        }

        // add in the new words that belong to the peak group

        query.exec(QString("insert into %0 (name) values('coordinating');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('subordinating');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('correlative');").arg(tableName));
        query.exec(QString("insert into %0 (name) values('conjunctive adverb');").arg(tableName));


        // get the highest current value
        query.exec(QString("SELECT MAX(id)FROM %0").arg(tableName));
        while(query.next())
        {
            end = query.value(0).toInt();
        }
        end++;

        for(int i = middle; i < end; i++)
        {
            query.exec(QString("insert into isArelationship (subject_table, subject_id, object_table, object_id) values(%0, %1, %0, %2);").arg(posID).arg(i).arg(peak));
        }

        // end of process


        //abstract
        int pos = getPosID("abstract");
        int text = AddTuple("text", "spelling", "relationship");

        cout << endl << endl;
        addNewWord(pos, text);
//))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))



//        cout << endl;
//        cout << "The new Tables table is " << endl;
//        query.exec(QString("SELECT * FROM Tables;"));
//        showTables();


//        cout << endl;
//        cout << "The new TableColumns table is " << endl;
//        displayTable("TableColumns");


        return 1;
    }



    //*****************************************************************************************************8
//    void testQuery(QString table)
//    {
//        cout << "Getting data" << endl << endl;
//        //int j = numberOfColumns(table);
//        query.exec(QString("select * from %1;").arg(table));
//        while (query.next())
//            {
//               for(int i = 0; i < j; i++)
//                {
//                    qDebug() << query.value(i).toString();
//                }
//                qDebug() << query.value(0).toString() << ".  " << query.value(1).toString() << ".  " << query.value(2).toString() << ".  " << query.value(3).toString();
//            }
//    }

private:
    QString getVariableType(int type, int size = 20)
    {
        //QString strSize = size.ToQString();
        switch (type)
        {
        case 1: return "int"; // SQLite integers are however many bytes it takes up to 8. For other versions of SQL, long int may be necessary.
            break;
        case 2: return QString("varchar(%0)").arg(size);
            break;
        case 3: return "real";
            break;
        case 4: return QString("char(%0)").arg(size);
            break;
        default: return "int";
        }
    }


};

#endif // CONTEMPLATION_H


